/**
 * Copyright  (c) 2009-2010 Wilmar IT Service,  Inc.   
 * All  rights  reserved. 
 */
package com.wilmar.process.procedure;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.SQLException;

import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleDriver;

import com.wilmar.process.core.ProcessSystemDeclareKeys;

/**
 * 所有存储过程的基类，所有子类可以通过该类获取Oracle数据库链接
 * 
 * @author MaKuBEX Email:makubexnet@gmail.com
 * @version 1.0.0 Apr 21, 2010
 * @since 1.0.0
 * 
 */
public abstract class BaseProcedure implements Serializable {

	/**
	 * @serialField
	 */
	private static final long serialVersionUID = 3101306833761036098L;

	/**
	 * 视图v_process_hist_pinst的名称
	 */
	public static final String VIEW_NAME_PROCESS_HIST_PINST = "v_process_hist_pinst";

	/**
	 * 表jbpm4_execution的名称
	 */
	public static final String TABLE_NAME_JBPM4_EXECUTION = "jbpm4_execution";

	/**
	 * 获取Oracle的数据库jdbc连接
	 * 
	 * @return Oracle数据库连接
	 * @throws SQLException
	 */
	protected static Connection getConnection() throws SQLException {
		Connection conn = new OracleDriver().defaultConnection();
		((OracleConnection) conn).setCreateStatementAsRefCursor(true);
		return conn;
	}

	/**
	 * 生成通用的sql in语句
	 * 
	 * @param propertyName
	 *            属性名
	 * @param valueArray
	 *            字符串数组
	 * @return in语句
	 */
	protected static String generateCommonInStatement(String propertyName,
			String[] valueArray) {
		StringBuffer generatedSql = new StringBuffer();
		if (valueArray != null && valueArray.length > 0
				&& valueArray[0] != null && valueArray[0].trim().length() > 0) {
			generatedSql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_AND);
			generatedSql.append(propertyName);
			generatedSql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_IN);
			generatedSql.append(ProcessSystemDeclareKeys.LEFT_PARENTHESES);
			for (int index = 0; index < valueArray.length; index++) {
				String value = valueArray[index];
				if (value != null && value.trim().length() > 0) {
					generatedSql.append(ProcessSystemDeclareKeys.SINGLE_QUOTES);
					generatedSql.append(value);
					generatedSql.append(ProcessSystemDeclareKeys.SINGLE_QUOTES);
					generatedSql.append(ProcessSystemDeclareKeys.COMMA);
				}
			}
			generatedSql.deleteCharAt(generatedSql.length() - 1);
			generatedSql.append(ProcessSystemDeclareKeys.RIGHT_PARENTHESES);
		}
		return generatedSql.toString();
	}

	/**
	 * 生成通用的sql in语句
	 * 
	 * @param propertyName
	 *            属性名称
	 * @param valueArray
	 *            整型数组
	 * @return 生成的in语句
	 */
	protected static String generateCommonInStatement(String propertyName,
			Integer[] valueArray) {
		StringBuffer generatedSql = new StringBuffer();
		if (valueArray != null && valueArray.length > 0
				&& valueArray[0] != null) {
			generatedSql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_AND);
			generatedSql.append(propertyName);
			generatedSql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_IN);
			generatedSql.append(ProcessSystemDeclareKeys.LEFT_PARENTHESES);
			for (int index = 0; index < valueArray.length; index++) {
				Integer value = valueArray[index];
				if (value != null) {
					generatedSql.append(value);
					generatedSql.append(ProcessSystemDeclareKeys.COMMA);
				}
			}
			generatedSql.deleteCharAt(generatedSql.length() - 1);
			generatedSql.append(ProcessSystemDeclareKeys.RIGHT_PARENTHESES);
		}
		return generatedSql.toString();
	}

	/**
	 * 生成通用的sql in语句
	 * 
	 * @param propertyName
	 *            属性名称
	 * @param valueArray
	 *            长整型数组
	 * @return 生成的in语句
	 */
	protected static String generateCommonInStatement(String propertyName,
			Long[] valueArray) {
		StringBuffer generatedSql = new StringBuffer();
		if (valueArray != null && valueArray.length > 0
				&& valueArray[0] != null) {
			generatedSql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_AND);
			generatedSql.append(propertyName);
			generatedSql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_IN);
			generatedSql.append(ProcessSystemDeclareKeys.LEFT_PARENTHESES);
			for (int index = 0; index < valueArray.length; index++) {
				Long value = valueArray[index];
				if (value != null) {
					generatedSql.append(ProcessSystemDeclareKeys.SINGLE_QUOTES);
					generatedSql.append(value);
					generatedSql.append(ProcessSystemDeclareKeys.SINGLE_QUOTES);
					generatedSql.append(ProcessSystemDeclareKeys.COMMA);
				}
			}
			generatedSql.deleteCharAt(generatedSql.length() - 1);
			generatedSql.append(ProcessSystemDeclareKeys.RIGHT_PARENTHESES);
		}
		return generatedSql.toString();
	}

	/**
	 * 生成通用的sql in语句
	 * 
	 * @param propertyName
	 *            属性名称
	 * @param valueArray
	 *            浮点型数组
	 * @return 生成的in语句
	 */
	protected static String generateCommonInStatement(String propertyName,
			Float[] valueArray) {
		StringBuffer generatedSql = new StringBuffer();
		if (valueArray != null && valueArray.length > 0
				&& valueArray[0] != null) {
			generatedSql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_AND);
			generatedSql.append(propertyName);
			generatedSql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_IN);
			generatedSql.append(ProcessSystemDeclareKeys.LEFT_PARENTHESES);
			for (int index = 0; index < valueArray.length; index++) {
				Float value = valueArray[index];
				if (value != null) {
					generatedSql.append(ProcessSystemDeclareKeys.SINGLE_QUOTES);
					generatedSql.append(value);
					generatedSql.append(ProcessSystemDeclareKeys.SINGLE_QUOTES);
					generatedSql.append(ProcessSystemDeclareKeys.COMMA);
				}
			}
			generatedSql.deleteCharAt(generatedSql.length() - 1);
			generatedSql.append(ProcessSystemDeclareKeys.RIGHT_PARENTHESES);
		}
		return generatedSql.toString();
	}

	/**
	 * 生成通用的sql in语句
	 * 
	 * @param propertyName
	 *            属性名称
	 * @param valueArray
	 *            双精度浮点型数组
	 * @return 生成的in语句
	 */
	protected static String generateCommonInStatement(String propertyName,
			Double[] valueArray) {
		StringBuffer generatedSql = new StringBuffer();
		if (valueArray != null && valueArray.length > 0
				&& valueArray[0] != null) {
			generatedSql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_AND);
			generatedSql.append(propertyName);
			generatedSql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_IN);
			generatedSql.append(ProcessSystemDeclareKeys.LEFT_PARENTHESES);
			for (int index = 0; index < valueArray.length; index++) {
				Double value = valueArray[index];
				if (value != null) {
					generatedSql.append(ProcessSystemDeclareKeys.SINGLE_QUOTES);
					generatedSql.append(value);
					generatedSql.append(ProcessSystemDeclareKeys.SINGLE_QUOTES);
					generatedSql.append(ProcessSystemDeclareKeys.COMMA);
				}
			}
			generatedSql.deleteCharAt(generatedSql.length() - 1);
			generatedSql.append(ProcessSystemDeclareKeys.RIGHT_PARENTHESES);
		}
		return generatedSql.toString();
	}

	/**
	 * 生成分页语句
	 * 
	 * @param generatedSql
	 *            已经生成的查询语句
	 * @param start
	 *            起始位置
	 * @param limit
	 *            分页信息
	 */
	protected static void appendPageinationStatement(StringBuffer generatedSql,
			int start, int limit) {
		StringBuffer sql = new StringBuffer();
		sql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_SELECT);
		sql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_ASTERISK);
		sql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_FROM);
		sql.append(ProcessSystemDeclareKeys.LEFT_PARENTHESES);
		sql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_SELECT);
		sql.append("record_");
		sql.append(ProcessSystemDeclareKeys.DOT);
		sql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_ASTERISK);
		sql.append(ProcessSystemDeclareKeys.COMMA);
		sql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_ROWNUMBER);
		sql.append("rownum_");
		sql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_FROM);
		sql.append(ProcessSystemDeclareKeys.LEFT_PARENTHESES);
		generatedSql.insert(0, sql.toString());
		generatedSql.append(ProcessSystemDeclareKeys.RIGHT_PARENTHESES);
		generatedSql.append(ProcessSystemDeclareKeys.SPACE);
		generatedSql.append("record_");
		generatedSql.append(ProcessSystemDeclareKeys.RIGHT_PARENTHESES);
		generatedSql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_WHERE);
		generatedSql.append("rownum_");
		generatedSql.append(ProcessSystemDeclareKeys.LESS_EQUAL);
		generatedSql.append(start + limit);
		generatedSql.append(ProcessSystemDeclareKeys.SQL_STATEMENT_AND);
		generatedSql.append("rownum_");
		generatedSql.append(ProcessSystemDeclareKeys.GREATER);
		generatedSql.append(start);
	}
}
